/*
 * Copyright 2012 Biz-e (info@biz-e.nl)
 *
 * Licensed under the Apache License, Version 2.0 (the "License");
 * you may not use this file except in compliance with the License.
 * You may obtain a copy of the License at
 *
 *   http://www.apache.org/licenses/LICENSE-2.0
 *
 * Unless required by applicable law or agreed to in writing, software
 * distributed under the License is distributed on an "AS IS" BASIS,
 * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
 * See the License for the specific language governing permissions and
 * limitations under the License.
 */
package nl.biz_e.android.calllog.dao;

import java.util.ArrayList;
import java.util.List;

import nl.biz_e.android.calllog.CallLogApp;
import nl.biz_e.android.calllog.model.CallLogItem;
import nl.biz_e.android.calllog.model.StoredLogItem;
import nl.biz_e.android.calllog.util.CallLogPrefs;
import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.DatabaseErrorHandler;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteException;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;

/**
 * TODO: Write type comment
 * 
 * @author Joost A. Bloemsma
 */
 class LogDBDao extends SQLiteOpenHelper {
	private static final String DEBUG_TAG = LogDBDao.class.getSimpleName();
	private static final int DATABASE_VERSION = 1;
	private static final String DATABASE_NAME = "logstorage";
    private static final String LOGGEDITEMS_TABLE = "loggeditems";
    private static final String COL_ROWID = "rowId";
    private static final String COL_ITEMID = "itemId";
    private static final String COL_CALID = "calId";
    private static final String COL_EVENTID = "eventId";
    private static final String COL_NOTES = "notes";
    private static final String COL_INCALENDAR = "incalendar";
    
    private static LogDBDao _instance = null;
    
    
    /**
	 * Factory method. Makes sure this is a singleton.
	 * @return Instance of this DAO
	 */
	public static LogDBDao getInstance() {
		if (_instance == null) {
			_instance = new LogDBDao(CallLogApp.getAppContext());
		}
		return _instance;
	}
	
	/**
	 * Constructs a new LogDBDao instance
	 * @param context
	 */
	private LogDBDao(Context context) {
		super(context, DATABASE_NAME, null, DATABASE_VERSION);
	}


	/**
	 * Constructs a new LogDBDao instance
	 * @param context
	 * @param errorHandler
	 */
	private LogDBDao(Context context,
			DatabaseErrorHandler errorHandler) {
		super(context, DATABASE_NAME, null, DATABASE_VERSION, errorHandler);
	}

	/**
	 * {@inheritDoc}
	 */
	@Override
	public void onCreate(SQLiteDatabase db) {
		String createLoggedItemTable =
			"CREATE TABLE IF NOT EXISTS " + LOGGEDITEMS_TABLE + " (" +
				COL_ROWID + " INTEGER PRIMARY KEY, " +
                COL_ITEMID + " TEXT, " +
                COL_CALID + " INTEGER, " + 
                COL_EVENTID + " INTEGER, " +
                COL_NOTES + " TEXT NULL, " +
                COL_INCALENDAR + " INTEGER, " +
                "UNIQUE (" + COL_ITEMID  + ", " + COL_CALID + ") ON CONFLICT FAIL);";
	
		db.execSQL(createLoggedItemTable);
		if (Log.isLoggable(DEBUG_TAG, Log.DEBUG)) Log.d(DEBUG_TAG, "Table " + LOGGEDITEMS_TABLE + " created for database " + DATABASE_NAME + ", version " + DATABASE_VERSION);
	}

	/**
	 * {@inheritDoc}
	 */
	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
		// First version, so nothing to do here...
	}
	
	public void storeItem(StoredLogItem item) throws DaoException {
		StoredLogItem orgItem = getItemByItemId(item.getItemId(), item.getCalId(), true);
		try {
			if (orgItem == null) {
				insertItem(item);
			} else {
				updateItem(orgItem, item);
			}
		} catch (DaoException e) {
			Log.e(DEBUG_TAG, e.getMessage(), e);
			throw e;
		}
	}
	
	private void updateItem(StoredLogItem orgItem, StoredLogItem newItem) throws DaoException {
		SQLiteDatabase db = this.getWritableDatabase();
		
		orgItem.setInCalendar(newItem.getInCalendar());
		orgItem.setNotes(newItem.getNotes());
		orgItem.setEventId(newItem.getEventId());
		
		ContentValues values = new ContentValues();
		values.put(COL_EVENTID, orgItem.getEventId());
		values.put(COL_NOTES, orgItem.getNotes());
		values.put(COL_INCALENDAR, orgItem.getInCalendar());
		
		try {
			db.update(LOGGEDITEMS_TABLE, 
					  values, 
					  COL_CALID + "=? AND " + COL_ITEMID + "=?", 
					  new String[] {orgItem.getCalId().toString(), orgItem.getItemId()}
					);
			if (Log.isLoggable(DEBUG_TAG, Log.DEBUG)) Log.d(DEBUG_TAG, "Updated item " + orgItem.getItemId());
		} catch (SQLiteException e) {
			throw new DaoException(e);
		}
	}
		
	private void insertItem(StoredLogItem item) throws DaoException {
		SQLiteDatabase db = this.getWritableDatabase();
		ContentValues values = new ContentValues();
		values.put(COL_CALID, item.getCalId());
		values.put(COL_ITEMID, item.getItemId());
		values.put(COL_EVENTID, item.getEventId());
		values.put(COL_NOTES, item.getNotes());
		values.put(COL_INCALENDAR, item.getInCalendar());
		try {
			long id = db.insert(LOGGEDITEMS_TABLE, null, values);
			item.setRowId(id);
		} catch (SQLException e) {
			throw new DaoException(e);
		} finally {
			db.close();
		}
		if (Log.isLoggable(DEBUG_TAG, Log.DEBUG)) Log.d(DEBUG_TAG, "Inserted item " + item.getItemId());
	}
	
	public StoredLogItem getItemByItemId(String itemId, Long calendar) {
		return getItemByItemId(itemId, calendar, false);
	}
	
	public StoredLogItem getItemByItemId(String itemId, Long calendar, boolean withNotes) {
		SQLiteDatabase db = getReadableDatabase();
		StoredLogItem item = null;
		String[] projection;
		
		if (withNotes) {
			projection = new String[] {COL_ROWID, COL_ITEMID, COL_CALID, COL_EVENTID, COL_NOTES};
		} else {
			projection = new String[] {COL_ROWID, COL_ITEMID, COL_CALID, COL_EVENTID};
		}
		
		Cursor cur = db.query(LOGGEDITEMS_TABLE, 
				projection, 
				COL_CALID + "=? AND " + COL_ITEMID + "=?",
				new String[] {calendar.toString(), itemId}, 
				null, null, null);
		if (cur != null) {
			if (cur.moveToFirst()) {
				item = new StoredLogItem(
						cur.getLong(0), 
						cur.getString(1), 
						cur.getLong(2), 
						cur.getLong(3));
				if (withNotes) item.setNotes(cur.getString(4));
			}
		}
		db.close();
		
		if (Log.isLoggable(DEBUG_TAG, Log.DEBUG)) {
			if (item == null) {
				Log.d(DEBUG_TAG, "No item found for " + itemId + " in current calendar");
			} else {
				Log.d(DEBUG_TAG, "Found item " + item.getItemId() + " by ID");
			}
		}
		return item;
	}
	
	public List<StoredLogItem> getItemsByCalendar(Long calendar) {
		return getItemsByCalendar(calendar, false);
	}
	
	public List<StoredLogItem> getItemsByCalendar(Long calendar, boolean withNotes) {
		List<StoredLogItem> items = new ArrayList<StoredLogItem>();
		SQLiteDatabase db = getReadableDatabase();
		String[] projection;
		if (withNotes) {
			projection = new String[] {COL_ROWID, COL_ITEMID, COL_CALID, COL_EVENTID, COL_NOTES};
		} else {
			projection = new String[] {COL_ROWID, COL_ITEMID, COL_CALID, COL_EVENTID};
		}
		
		Cursor cur = db.query(LOGGEDITEMS_TABLE, 
				projection, 
				COL_CALID + "=?",
				new String[] {calendar.toString()}, 
				null, null, null);
		if (cur != null) {
			while (cur.moveToNext()) {
				StoredLogItem item = new StoredLogItem(
						cur.getLong(0), 
						cur.getString(1), 
						cur.getLong(2), 
						cur.getLong(3));
				if (withNotes) item.setNotes(cur.getString(4));
				items.add(item);
			}
		}
		db.close();
		if (Log.isLoggable(DEBUG_TAG, Log.DEBUG)) Log.d(DEBUG_TAG, "Found " + items.size() + " items for calendar " + calendar.toString());
		return items;
	}
	
	public void deleteItem(StoredLogItem item) {
		SQLiteDatabase db = getWritableDatabase();
		if (item.getRowId() != null) {
			db.delete(LOGGEDITEMS_TABLE, 
					COL_ROWID + "=?", 
					new String[] {item.getRowId().toString()});
			if (Log.isLoggable(DEBUG_TAG, Log.DEBUG)) Log.d(DEBUG_TAG, "Deleted item " + item.getItemId() + " by rowId");
		} else {
			db.delete(LOGGEDITEMS_TABLE,
					COL_CALID + "=? AND " + COL_ITEMID + "=?",
					new String[] {item.getCalId().toString(), item.getItemId()});
			if (Log.isLoggable(DEBUG_TAG, Log.DEBUG)) Log.d(DEBUG_TAG, "Deleted item " + item.getItemId() + " by primary key");
		}
		db.close();
	}

	public boolean isLoggedItem(CallLogItem item) {
		boolean isInCalendar = false;
		StoredLogItem logItem = getItemByItemId(item.getItemId(), CallLogPrefs.getCalendarId());
		if (logItem != null) isInCalendar = (logItem.getInCalendar() > 0);
		return isInCalendar;
	}
	
	public String getNotes(CallLogItem item, long calendar) {
		String notes = "";
		StoredLogItem logItem = getItemByItemId(item.getItemId(), calendar, true);
		if (logItem != null) notes = logItem.getNotes();
		if (Log.isLoggable(DEBUG_TAG, Log.DEBUG)) Log.d(DEBUG_TAG, "Retrieved notes for item " + item.getItemId() + " from calendarId " + calendar);
		return notes;
	}
}

